Database Index Tuning

Summary

Database index tuning can increase the performance of high-volume Sitecore solutions to improve the user experience for both editors and the viewing public. This article applies primarily to Sitecore implementations using SQL Server 2005, though similar techniques could be applied on solutions using other supported RDBMS technologies. The specific techniques described in this resource have been shown to improve performance by up to 100 times for certain operations.

Instructions for Sitecore CMS 5.3.1

Note: this section applies to Sitecore versions through 5.3.1 070924. The upgrade process from 070924 and earlier does not apply these changes automatically (they should be applied manually after the upgrade).

Recommended Database Changes to Improve Performance

For the SharedFields table in All Sitecore Databases:
- Change the ndxLookup index from Nonclustered to Clustered
- Configure Fill Factor for the ndxLookup index to between 50% and 75%
- Enable Pad Index for the ndxLookup index
For the VersionedFields table in All Sitecore Databases:
- Create a clustered index named ndxItemIdon the column ItemId
- Configure Fill Factor for the ndxItemId index to between 50% and 75%
- Enable Pad Index for the ndxItemId
For the UnversionedFields table in All Sitecore “Master” databases:
- Create a clustered index named ndxItemId on the column ItemId
- Configure Fill Factor for the ndxItemId index to between 50% and 75%
- Enable Pad Index for the ndxItemId index

Tuning Indexes in Management Studio

Updating the Type of an Existing Index (from Nonclustered to Clustered)

To update properties of an existing index take the following action;

Expand Indexes under the table
Double-click on the index
Change the Index type property on the General tab.

Configuring the Fill Factor or enabling Pad Index for an Existing Index

To set the Fill Factor or enable Pad Index for an existing index take the following steps;

Expand Indexes under the table
Double-click the index
Select the Options tab
Select the Set fill factor checkbox and enter a percentage
Select the Pad index checkbox if appropriate.
Acknowledge the index properties
Right-click on the index and choose Rebuild.

Creating a New Index

To create a new index take the following steps;

Right-click Indexes under the table
Choose New Index.
On the General tab enter the Index name then select the Index type
Press Add and select the column(s) to be indexed.
If specifying a Fill factor or enabling Pad Index, select the Options tab, select the Set fill factor checkbox and enter a percentage.
Select the Pad index checkbox if appropriate.

Index Maintenance

Sitecore recommends rebuilding clustered indexes daily using T-SQL using the following format for each index:

DBCC DBREINDEX('<tablename>','IndexName',<FillFactor>)

Where the first parameter specifies a database table (mandatory), the second parameter specifies which index of that table is to be rebuilt (a blank causes all indexes to be rebuilt) and the third parameter specifies the fill factor. For instance to rebuild all indexes for the VersionedFields table using a fill factor of 75 you would use the following string:

DBCC DBREINDEX('VersionedFields',' ',75)

Staging Considerations

Apply indexing as appropriate for all databases including those employed by both Sitecore CMS (Staging Master) and Content Delivery (Staging Slave) servers.

References

For more information on SQL Server indexing, Fill Factor and Pad Index, please see:

Instructions for Sitecore CMS 6.x

Note that you do NOT need to apply instructions for Sitecore CMS 5.3 mentioned above to Sitecore CMS 6 and later solutions.

You should apply the instructions in the SQL Server Index Fragmentation Level and the Database Properties sections that are described in the OMS Performance Tuning Guide to all Sitecore content databases:
http://sdn.sitecore.net/Reference/Sitecore%206/OMS%20Performance%20Tuning%20Guide.html

Database Options

Database options at all Sitecore databases should be set as it is presented on the following screenshot (the important settings are marked with red):

database properties

Note: Set the compatibility level according to your SQL server: 100 for SQL Server 2008 and 90 for SQL Server 2005.
Note: You can set any other Recovery model values but this will dramatically increase the size of the active database.

Prev Next